#!/usr/bin/env python
# -*- coding:utf-8 -*-
# @Time   : 2021/4/6 20:51
# @Author : cjw
"""
数据库连接
安装包：aiomysql、cx_Oracle
"""
from setting.setting import DATABASE_INI_PATH
from util.file_reader import INIReader
from aiomysql import create_pool, DictCursor
from cx_Oracle import SessionPool
from asyncio import get_event_loop, ensure_future
from typing import List


class DataBase:

    def __init__(self, database: str = 'mysql', autocommit: bool = True, *args, **kwargs):
        """
        数据库初始化
        :param database: 数据库类型
        :param autocommit: 是否自动提交，默认是True
        :param args:
        :param kwargs:
        """
        self._args, self._kwargs = args, kwargs
        self._autocommit = autocommit
        if database.lower() == 'mysql':
            self._database = create_pool
            self._ini = INIReader(DATABASE_INI_PATH).data
            self._loop = get_event_loop()
            self._mysql_pool = self.mysql_pool
        if database.lower() == 'oracle':
            self._database = SessionPool
            self._ini = INIReader(DATABASE_INI_PATH, section='oracle').data
            self._oracle_pool = self.oracle_pool

    @property
    def oracle_pool(self):
        return self._database(*self._args, **self._ini, **self._kwargs)

    @property
    def mysql_pool(self):
        """mysql异步连接池"""
        self._ini['autocommit'] = self._autocommit
        pool_task = ensure_future(self._database(*self._args, **self._ini, **self._kwargs))
        self._loop.run_until_complete(pool_task)
        return pool_task.result()


class MysqlClient(DataBase):

    @classmethod
    def setup(cls, *args, **kwargs):
        """
        类的实例化方法，MysqlClient.setup() 等价于 MysqlClient()
        :param args:
        :param kwargs:
        :return:
        """
        return cls(*args, **kwargs)

    async def _select(self, sql: str, param: tuple = (), rows: [int, None] = 1):
        """
        数据库查询操作
        :param sql: 查询sql
        :param param: 查询参数
        :param rows: 默认返回一行查询数据，如果为空None，则返回所有数据
        :return: 返回字典列表的数据
        """
        # 1、从异步的数据库连接池中获取异步的数据库连接
        # 使用上下文自动关闭数据库连接
        async with self._mysql_pool.acquire() as conn:
            # 2、通过数据库连接获取数据库游标,DictCursor:字典游标
            async with conn.cursor(DictCursor) as cur:
                # 3、执行sql语句
                await cur.execute(sql.replace('?', '%s'), param)
                # 4、对sql执行结果进行处理
                if rows:
                    rs = await cur.fetchmany(rows)
                else:
                    rs = await cur.fetchall()
        return rs

    def select(self, *args, **kwargs):
        """执行异步函数，异步函数需要在事件循环中执行，并且动态的获取执行结果"""
        self._loop.run_until_complete(select_task := ensure_future(self._select(*args, **kwargs)))
        return select_task.result()

    async def _execute(self, sql: str, param: tuple = ()):
        """
        执行update、insert、delete等语句的操作
        :param sql: 执行的sql语句
        :param param: sql语句参数
        :return: 返回执行sql后，被影响的行数
        """
        async with self._mysql_pool.acquire() as conn:
            async with conn.cursor() as cur:
                await cur.execute(sql.replace('?', '%s'), param)
                return cur.rowcount

    def execute(self, *args, **kwargs):
        self._loop.run_until_complete(execute_task := ensure_future(self._execute(*args, **kwargs)))
        return execute_task.result()


class OracleClient(DataBase):

    @classmethod
    def setup(cls, *args, **kwargs):
        return cls('oracle', *args, **kwargs)

    def select(self, sql: str, param: [list, None] = None, rows: [int, None] = 1, **kwargs):
        """
        oracle同步查询,
        :param sql: 查询sql
        :param param: 查询参数，参数传递可以是列表形式的，也可以是关键字参数形式传入
        :param rows: 显示的结果的行数
        :param kwargs:
        :return:
        """
        if param and kwargs:
            raise Exception(f'查询参数类型不能同时传入：{param}, {kwargs}')
        # 获取数据库连接池连接,根据查询参数和查询数量获取返回查询结果
        with self._oracle_pool.acquire() as conn:
            with conn.cursor() as cur:
                cur.execute(sql, (param or kwargs))
                columns = [col[0] for col in cur.description]  # 获取表字段列表
                cur.rowfactory = lambda *args: dict(zip(columns, args))  # 更改查询返回的数据格式，使用字典形式
                if rows:
                    rs = cur.fetchone() if rows == 1 else cur.fetchmany(rows)
                else:
                    rs = cur.fetchall()
        return rs

    def execute(self, sql: str, param: List[tuple], **kwargs):
        """

        :param sql: sql语句
        :param param: sql语句参数，类型：[(),]
        :param kwargs: 关键字sql语句参数
        :return:
        """
        with self._oracle_pool.acquire() as conn:
            with conn.cursor() as cur:
                if param:
                    cur.executemany(sql, param)
                else:
                    cur.execute(sql, **kwargs)
                rowcount = cur.rowcount
            conn.commit()
        return rowcount


if __name__ == '__main__':
    # mysql_client = MysqlClient.setup()
    # print(mysql_client.select(r'show databases;', (), None))
    # print(mysql_client.select(r'select * from zt_bug where id = ?', (1,), None))
    # print(mysql_client.execute(r'update zt_bug set title=? where id =?', ('演示bug', 1)))

    oracle_client = OracleClient.setup()
    print(oracle_client.select(r'select * from table where id = :id and name = :sam', [1, 'sam']))
    print(oracle_client.select(r'select * from table where id = :id and name = :sam', id=1, sam='sam'))
    print(oracle_client.execute(r'update table set name = :sam where id = :id', [('sam', 1), ('sam2', 2)]))
    print(oracle_client.execute(r'update table set name = :sam where id = :id', sam='sam', id=1))
